from flask import jsonify
from apps import dbUtil
from apps.GPS.gpsService import save_gps
from datetime import datetime
from apps.dbUtil import datetime_toString, cur_datetime_String
from apps.gpsAndPathUtils import deal_cors, returnGpsByPathId
from apps.trailer.trailerService import modify_state_by_trailer_id


def get_last_position_service(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    print(trailer_ID)
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute(
        'select GPS_ID from gps where path_ID=(SELECT path_ID FROM path WHERE trailer_ID = "{}" ORDER BY path_ID desc limit 1) ORDER BY GPS_ID desc limit 1;'.format(trailer_ID))

    # 使用fetall()获取全部数据
    data = cursor.fetchall()
    print(data)
    if len(data) == 0:
        return jsonify({"code": 200, "data": []})
    # 关闭游标和数据库的连接
    else:
        GPS_ID = data[0][0]
        print(GPS_ID)
        cursor.execute('SELECT longitude,latitude FROM gps WHERE GPS_ID = "' + str(GPS_ID) + '";')
        data = cursor.fetchall()
        cursor.close()
        db.close()
        print(data)
        return deal_cors(jsonify({"code": 200, "data": (data[0][0], data[0][1])}))


# 4. 轨迹回放：根据拖车号和轨迹号，返回该轨迹的所有GPS数据，[[1,2],...]
def trace_back_service(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT path_ID FROM path WHERE trailer_ID = "' + str(trailer_ID) + '" ORDER BY path_ID desc;')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()
    if len(data) == 0:
        return jsonify({"code": 200, "data": []})
    # 关闭游标和数据库的连接
    else:
        res = []
        for path_ID in data:
            print(path_ID[0])
            one_path = []
            cursor.execute('SELECT longitude,latitude FROM gps WHERE path_ID = ' + str(path_ID[0]) + ';')
            print('SELECT longitude,latitude FROM gps WHERE path_ID = ' + str(path_ID[0]) + ';')

            gps = cursor.fetchall()
            # print(gps)
            for road in gps:
                one_path.append((road[0], road[1]))
            res.append(one_path)
        data = cursor.fetchall()
        cursor.close()
        db.close()

        return deal_cors(jsonify({"code": 200, "data": res}))


def getTrailerStartTimeAndTotalTime(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('select start_time,total_time from path where trailer_ID="'+trailer_ID+'" ORDER BY path_ID DESC limit 1; ')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    if len(data) == 0:
        cursor.close()
        db.close()
        return "have no record", "have no record"
    else:
        cursor.close()
        db.close()
        return str(data[0][0]), str(data[0][1])


def getTrailerPath(trailer_ID):
    if trailer_ID is None:
        trailer_ID = ""
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()
    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT path_ID FROM path WHERE trailer_ID = "' + str(trailer_ID) + '" ORDER BY path_ID ASC;')
    # 使用fetall()获取全部数据
    data = cursor.fetchall()
    if len(data) == 0:
        return deal_cors(jsonify({"code": 200, "data": []}))
    # 关闭游标和数据库的连接
    else:
        paths = []
        for temp in data:
            paths.append(temp[0])
        cursor.close()
        db.close()
        return deal_cors(jsonify({"code": 200, "data": paths}))


def returnGpsByPathIdService(path_ID):
    return deal_cors(jsonify({"code": 200, "data": returnGpsByPathId(path_ID)}))


# 插入path
def insert_path(path_id, start_position, start_time, trailer_id):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    # 使用execute()方法执行SQL语句
    cursor.execute('insert into path(path_ID, start_time, trailer_id) value ("{}","{}","{}");'.format(path_id, start_time, trailer_id))
    db.commit()
    # 插入GPS
    gps_id = save_gps(path_id, start_position)
    # 修改path中的开始地点
    modify_path_start_position(path_id, gps_id)
    cursor.close()
    db.close()


# 修改path的开始地点
def modify_path_start_position(path_id, gps_id):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    # 使用execute()方法执行SQL语句
    cursor.execute("update path set start_position='{}' where path_ID='{}'".format(gps_id, path_id))
    db.commit()
    cursor.close()
    db.close()


# 修改path的结束地点
def modify_path_end_position(path_id, gps_id):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    # 使用execute()方法执行SQL语句
    cursor.execute("update path set end_position='{}' where path_ID='{}'".format(gps_id, path_id))
    db.commit()
    cursor.close()
    db.close()


# 修改path的结束点和时间
def path_end_action(path_id, end_position, end_time, trailer_id):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    # 插入GPS
    gps_id = save_gps(path_id, end_position)
    # 修改path中的开始地点
    modify_path_end_position(path_id, gps_id)
    # 查询path_id对应的开始时间
    cursor.execute("select start_time from path where path_ID='{}'".format(path_id))
    start_time = cursor.fetchall()[0][0]
    # start_date = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
    end_date = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")
    total_time = str(end_date - start_time)
    # 修改path中的total_time
    modify_path_total_time(path_id, total_time)
    # 修改拖车状态
    modify_state_by_trailer_id(trailer_id, 2)
    cursor.close()
    db.close()


# 修改path的结束时间
def modify_path_total_time(path_id, total_time):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    # 使用execute()方法执行SQL语句
    cursor.execute("update path set total_time='{}' where path_ID='{}'".format(total_time, path_id))
    db.commit()
    cursor.close()
    db.close()


if __name__ == '__main__':
    insert_path("100", [11, 12], "2021-12-14 17:34:56", "001")